package com.lyn.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.lyn.kit.PathKit;

/**
 * 创建空的excel文件，并写入数据
 *
 */
public class Excel03Service {
	private HSSFWorkbook workbook;
	private HSSFSheet activeSheet;
	private int activeSheetIndex; //从0开始计算
	private FileOutputStream fos;
	private FileInputStream fis;
	private String fileBasePath; //文件存储路径
	
	/**
	 * 创建excel
	 */
	public void createExcel() {
		workbook = new HSSFWorkbook();
	}
	
	
	/**
	 * 向excel中添加sheet
	 */
	public void createSheet(String sheetName) {
		activeSheet = workbook.createSheet(sheetName);
		this.activeSheetIndex = workbook.getActiveSheetIndex();
	}
	
	/**
	 * 创建excel，同时添加sheet
	 */
	public void createExcelWithSheet(String... sheetNames) {
		workbook = new HSSFWorkbook();
		for(String str : sheetNames) {
			workbook.createSheet(str);
		}
		this.activeSheetIndex = workbook.getActiveSheetIndex();
		this.activeSheet = workbook.getSheetAt(activeSheetIndex);
	}

	
	
	/**
	 * 打开已存在的excel文件
	 */
	public void openExcel(String excelFileName) {
		if(fileBasePath==null) {
			fileBasePath = PathKit.getRootClassPath();
		}
		try {
			String filePath = fileBasePath+File.separator+excelFileName;
			//创建输入流
	        fis = new FileInputStream(new File(filePath));
	        //由输入流得到工作簿
	        workbook = new HSSFWorkbook(fis);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void getSheetByIndex(int index) {
		activeSheet = workbook.getSheetAt(index);
	}
	
	public void getSheetByName(String name) {
		activeSheet = workbook.getSheet(name);
	}
	
	/**
	 * 获取行大行号，从0开始计数，最大行号是最后一次出现内容的行
	 */
	public int getMaxRowNum() {
		return activeSheet.getLastRowNum();
	}
	
	/**
	 * 读取excel中单元格的内容
	 * @param rowIndx 行：从0开始
	 * @param columnIndex 列：从0开始
	 * @return cell的内容
	 */
	public String readFromCell(int rowIndx, int columnIndex) {
		HSSFRow row = activeSheet.getRow(rowIndx);
		HSSFCell cell = row.getCell(columnIndex);
        String cellValue = cell.getStringCellValue();
        return cellValue;
	}
	
	/**
	 * 读取excel中单元格的内容
	 * @param activeSheetIndex 第几个sheet 从0开始
	 * @param rowIndx 行：从0开始
	 * @param columnIndex 列：从0开始
	 * @return cell的内容
	 */
	public String readFromCell(int activeSheetIndex, int rowIndx, int columnIndex) {
		activeSheet = workbook.getSheetAt(activeSheetIndex);
		this.activeSheetIndex = activeSheetIndex;
		HSSFRow row = activeSheet.getRow(rowIndx);
		HSSFCell cell = row.getCell(columnIndex);
        String cellValue = cell.getStringCellValue();
        return cellValue;
	}
	
	/**
	 * 把数据写入单元格
	 * @param rowIndx 行：从0开始
	 * @param columnIndex 列：从0开始
	 * @value 内容
	 */
	public void writeToCell(int rowIndex, int columnIndex, String value) {
		HSSFRow row = activeSheet.createRow(rowIndex);
		HSSFCell cell = row.createCell(columnIndex);
		cell.setCellValue(value);
	}
	
	/**
	 * 把数据写入单元格
	 * @param activeSheetIndex 第几个sheet 从0开始
	 * @param rowIndx 行：从0开始
	 * @param columnIndex 列：从0开始
	 * @value 内容
	 */
	public void writeToCell(int activeSheetIndex, int rowIndex, int columnIndex, String value) {
		this.activeSheetIndex = activeSheetIndex;
		HSSFRow row = workbook.getSheetAt(this.activeSheetIndex).createRow(rowIndex);
		HSSFCell cell = row.createCell(columnIndex);
		cell.setCellValue(value);
	}
	
	/**
	 * 把更改的内容保存到文件
	 */
	public HSSFWorkbook saveToFile(String FileName) {
		try {
			if(this.fileBasePath==null) {
				fileBasePath = PathKit.getRootClassPath();
			}
			String filePath = fileBasePath+File.separator+FileName;
	        fos = new FileOutputStream(new File(filePath));
	        workbook.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return workbook;
	}
	
	/**
	 * 清除资源
	 */
	public void cleanResource() {
		try {
			if(workbook!=null) {
				workbook.close();
			}
			if(fos!=null) {
				fos.close();
			}
			if(fis!=null) {
				fis.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public HSSFWorkbook getWorkbook() {
		return workbook;
	}

	public void setWorkbook(HSSFWorkbook workbook) {
		this.workbook = workbook;
	}
	
	public HSSFSheet getActiveSheet() {
		return activeSheet;
	}

	public void setActiveSheet(HSSFSheet activeSheet) {
		this.activeSheet = activeSheet;
	}

	public int getActiveSheetIndex() {
		return activeSheetIndex;
	}

	public void setActiveSheetIndex(int activeSheetIndex) {
		workbook.setActiveSheet(activeSheetIndex);
		this.activeSheetIndex = activeSheetIndex;
	}

	public String getFileBasePath() {
		return fileBasePath;
	}

	public void setFileBasePath(String fileBasePath) {
		this.fileBasePath = fileBasePath;
	}
}
